package com.example.board.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.board.pojo.User;
import com.example.board.pojo.dto.DeptDto;
import com.example.board.pojo.dto.UserDto;
import com.example.board.pojo.dto.UserRecordDto;
import com.example.board.pojo.vo.NumberAmount;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface RegionMapper extends BaseMapper<User> {

    @Select({
            "SELECT sum(o.amount) amount ",
            "FROM sd_order o where o.order_state in(2,3,13) and o.on_cancel  = 0  "
    })
    String findAllRegionCity();


    @Select({

            "<script>",
            " SELECT ",
            "   DATE_FORMAT( sd.created_at, '%d' ) AS name,  ",
            "             count(sd.id) as data  ",
            "FROM ",
            " sd_visit_user_record sd ,sd_tool_man_info su  ",
            "<where> 1=1 ",

            "<if test='province != null and province != \"\" '> ",
            "and su.province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and su.city = #{region} ",
            "</if> ",
            "and ",
            " sd.tool_man_id = su.user_id  ",
            " ",
            "and sd.created_at >= #{startDate} ",
            "</where> ",
            "GROUP BY name ",
            "ORDER BY name ",

            "</script>",
    })
    List<NumberAmount> findVistWeakRate(@Param("province")String province,
                                        @Param("region")String region,
                                        @Param("startDate") LocalDateTime localTime);

    @Select({

            "<script>",
            " SELECT ",
            "   DATE_FORMAT( sd.created_at, '%d' ) AS name,  ",
            "             count(sd.id) as data   ",
            "FROM ",
            " sd_user_record sd ,sd_tool_man_info su  ",
            "<where> 1=1 ",

            "<if test='province != null and province != \"\" '> ",
            "and su.province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and su.city = #{region} ",
            "</if> ",
            "and ",
            " sd.tool_man_id = su.user_id  ",
            " ",
            "and sd.created_at >= #{startDate} ",
            "</where> ",
            "GROUP BY sd.created_at ",
            "ORDER BY  sd.created_at  ",

            "</script>",
    })
    List<NumberAmount> findVistWeakRate1(@Param("province")String province,
                                         @Param("region")String region,
                                         @Param("startDate")LocalDateTime localTime);

    @Select({

            "<script>",
            " SELECT ",
            "   DATE_FORMAT( sd.order_time, '%d' ) AS name,  ",
            "             count(sd.id) as data  ",
            "FROM ",
            " sd_order sd ,sd_tool_man_info su  ",
            "<where> 1=1 ",

            "<if test='province != null and province != \"\" '> ",
            "and su.province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and su.city = #{region} ",
            "</if> ",
            "and ",
            " sd.tool_man_id = su.user_id  ",
            " ",
            "and sd.created_at >= #{startDate} ",
            "</where> ",
            "GROUP BY name ",
            "ORDER BY name ",

            "</script>",
    })
    List<NumberAmount> findOrderWeakRate(@Param("province")String province,
                                         @Param("region")String region,
                                         @Param("startDate")LocalDateTime localTim);


    @Select({
            " SELECT ",
            "u.shop_city district" ,
            "FROM ",
            " sd_visit_user_record v , sd_user_record u ",
            " where ",
            " v.user_record_id = u.id  ",
            "and u.shop_province = #{province} ",
            "GROUP BY ",
            " u.shop_city ",

    })
    List<String> findCity(@Param("province") String province);


    @Select({
            "<script>",
            " SELECT ",
            " count(v.id) date,u.shop_province province ,u.shop_city city" ,
            "FROM ",
            " sd_visit_user_record v , sd_user_record u ",
            "<where> 1=1 ",


            " and u.shop_province = #{province} ",

            "and u.shop_city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",

            " and ",

            " v.user_record_id = u.id  ",
            " and v.created_at between #{startDate} and #{endDate}",

            "</where>",
            "GROUP BY ",
            "  u.shop_province ,u.shop_city ",

            "</script>"
    })
    List<UserDto> findConversion(@Param("province") String province,
                           @Param("cities") List<String> cityList,
                           @Param("startDate") LocalDateTime startDate,
                           @Param("endDate") LocalDateTime endDate);


    @Select({
            "<script>",
            " SELECT ",
            " count(u.id) date,u.shop_province province ,u.shop_city city" ,
            "FROM ",
            "  sd_user_record u ",
            "<where> 1=1 ",

            "and u.shop_province = #{province} ",

            "and u.shop_city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",

            " and ",

            " u.created_at between #{startDate} and #{endDate}",

            "</where>",
            "GROUP BY ",
            "  u.shop_province ,u.shop_city ",

            "</script>"
    })
    List<UserDto> findUserRe(@Param("province") String province,
                       @Param("cities") List<String> cityList,
                       @Param("startDate") LocalDateTime startDate,
                       @Param("endDate") LocalDateTime endDate);


    @Select({
            "<script>",
            "SELECT ",
            "  IFNULL(count(o.id),0) date,o.receiver_province province ,o.receiver_city city ",
            "from sd_order o  ",
            " <where> 1=1  ",

            "and o.receiver_province = #{province} ",

            "and o.receiver_city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",


            "and o.order_state in (2,3,12) and o.on_cancel  = 0  ",
            " and o.order_time  between #{startDate} and #{endDate} ",
            " </where> ",
            "GROUP BY  o.receiver_province,o.receiver_city",
            "</script>"
    })
    List<UserDto> findOrders(@Param("province") String province,
                       @Param("cities") List<String> cityList,
                       @Param("startDate") LocalDateTime startDate,
                       @Param("endDate") LocalDateTime endDate);

    @Select({
            "<script>",
            " SELECT ",
            "  sum(o.amount) date,o.receiver_province province ",
            "     ",
            "FROM ",
            " sd_order o  ",
            "<where> 1=1 ",
            " and o.receiver_province = #{province} ",
            " and o.order_time >= #{startDate} and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "</where> ",
            "GROUP BY o.receiver_province",

            "</script>",
    })
    UserDto findRegionRank1(@Param("province") String province,
                            @Param("startDate")LocalDateTime startLocalTime);



    @Select({
            "<script>",
            " SELECT ",
            "  sum(o.amount) date,o.receiver_province province, o.receiver_city city ",
            "     ",
            "FROM ",
            " sd_order o  ",
            "<where> 1=1 ",
            " and o.receiver_province = #{province} ",
            " and  o.receiver_city in ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",

            " and o.order_time >= #{startDate} and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "</where> ",
            "GROUP BY o.receiver_province, o.receiver_city ",

            "</script>",
    })
    List<UserDto> findRegionRank(@Param("province") String province,
                           @Param("cities") List<String> cityList,
                           @Param("startDate") LocalDateTime startDate);

    @Select({
            " SELECT ",
            "o.receiver_city city " ,
            "FROM ",
            " sd_order o  ",
            " where ",
            " o.receiver_province = #{province} ",
            "GROUP BY ",
            " o.receiver_city ",

    })
    List<String> findCityOrder(@Param("province")String province);

    @Select({
            "<script>",
            "SELECT  count(o.id ) total,",
            "o.receiver_province  province,o.receiver_city city,d.customer_number idNumber,",
            "max(o.order_time) lastOrderTime,o.receiver_shop_name shopName,",
            "o.tool_man_name userName,o.total_number goodsNumber ",
            " ,o.buyer_avatar  img",
            "from ",
            "sd_order o ,sd_user_record d ",
            "<where> 1=1 ",

            "<if test='region != null and region != \"\" '> ",
            "and o.receiver_city  = #{region} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",
            " and ",
            " o.order_state in(2,3,12)  and  o.on_cancel  = 0 ",
            " and o.user_record_id is not null " ,
            "and o.tool_man_id is not null and o.user_record_id = d.id ",
            "</where> ",
            "GROUP BY  o.receiver_province,o.receiver_city,d.customer_number,o.receiver_shop_name,o.tool_man_name,o.total_number,o.buyer_avatar ",
            "ORDER BY max(o.order_time)   ",
            "</script> "

    })
    List<UserRecordDto> getReCustomerIOrder(@Param("province")String province,
                                            @Param("region")String region);

    @Select({
            "<script>",
            "SELECT  t.*" ,
            "from ( SELECT t.buyer_avatar img,t.receiver_shop_name shopName,",
            "t.receiver_province province,t.receiver_city city,",
            "t.tool_man_name salesman, ",
            "t.order_time lastOrderTime,d.customer_number idNumber ,",
            "row_number() over (partition by d.customer_number order by t.order_time ) as seqnum ",
            "from  ",
            "sd_order t ,sd_user_record d  ",
            "<where> 1=1 ",

            "<if test='region != null and region != \"\" '> ",
            "and t.receiver_city = #{region} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and t.receiver_province= #{province} ",
            "</if> ",

            " and ",
            " t.order_state in(2,3,12) and t.on_cancel  = 0 and t.user_record_id = d.id ",
            "GROUP BY  t.buyer_avatar,t.receiver_shop_name,t.receiver_province,t.receiver_city,t.tool_man_name , t.order_time,d.customer_number ",
            "ORDER BY d.customer_number ",
            "</where> )t  where  seqnum <![CDATA[ <= ]]> 2",

            "</script> "

    })
    List<UserRecordDto> findReCustomerTime(@Param("province")String province,
                                           @Param("region")String region);



//    @Select({
//            " SELECT ",
//            "o.receiver_city district" ,
//            "FROM ",
//            " sd_order o",
//            " where ",
//            " o.receiver_province = #{province} ",
//            "GROUP BY ",
//            " o.receiver_city  ",
//
//    })
@Select({
        "SELECT   sd.city  district ",
        "FROM sd_tool_man_info sd ,sys_user su ",
        "WHERE sd.user_id = su.id and sd.province = #{province}  ",
        "GROUP BY  sd.city  "
})
    List<String> findAllCity(String province);


    @Select({
            "<script>",
            " SELECT ",
            " count(distinct u.dept_id) total ,sd.province province ,sd.city city " ,
            "  FROM sys_user u,sd_tool_man_info sd  , sys_dept de ",
            "<where> 1=1 ",

            "and sd.province = #{province} and u.id = sd.user_id and u.dept_id = de.id ",

            "and sd.city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",


            "<if test='teamName != null and teamName != \"\" '> ",
            "and de.name = #{teamName} ",
            "</if> ",

            " and ",

            " de.created_at &lt; #{endDate}",

            "</where>",
            "GROUP BY ",
            "  sd.province ,sd.city  ",

            "</script>"
    })
    List<DeptDto> findDistributorNumber( @Param("province")String province,
                                        @Param("cities") List<String> cityList,
                                        @Param("endDate") LocalDateTime endDate,
                                         @Param("teamName")String teamName);



    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(o.id),0)  totalOrder ,sum(o.amount) transactionAmount,o.receiver_province province,o.receiver_city city ",
            " FROM ",
            " sd_order o ,sys_user u,sys_dept d ",
            "<where> 1=1 ",

            "and o.receiver_province = #{province} and o.tool_man_id = u.id and u.dept_id = d.id ",

            "and o.receiver_city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and d.name= #{teamName} ",
            "</if> ",


            "and o.order_time BETWEEN #{startDate} and #{endDate} and o.order_state  in (2,3,12) and o.on_cancel = 0 ",
            "</where> ",
            "GROUP BY ",
            "  o.receiver_province ,o.receiver_city  ",
            "</script>",
    })
    List<DeptDto> findDistributors( @Param("province")String province,
                                   @Param("cities")List<String> cityList,
                                   @Param("status") String status,
                                   @Param("startDate") LocalDateTime startDate,
                                   @Param("endDate") LocalDateTime endDate,
                                   @Param("teamName") String teamName);




    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(sr.id),0) total, sr.shop_province province ,sr.shop_city city  ",
            "FROM ",
            " sd_user_record sr ,sys_user u ,sys_dept d " ,
                    " ",
            "<where> 1=1 and sr.tool_man_id = u.id and u.dept_id = d.id ",

            "and sr.shop_province = #{province} ",

            "and sr.shop_city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and d.name= #{teamName} ",
            "</if> ",

            "and sr.created_at BETWEEN #{startDate} and #{endDate}",
            "</where> ",
            " GROUP BY ",
            "  sr.shop_province ,sr.shop_city  ",
            "</script>",
    })
    List<DeptDto> findURMoth(@Param("province")String province,
                                @Param("cities")List<String> cityList,
                                @Param("status") String status,
                                @Param("startDate") LocalDateTime startDate,
                                @Param("endDate") LocalDateTime endDate,
                                @Param("teamName") String teamName);


    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(o.id),0) total, o.receiver_province province ,o.receiver_city city  ",
            "FROM ",
            " sd_order o ,sys_user u ,sys_dept d " ,
            " ",
            "<where> 1=1 and o.tool_man_id = u.id and u.dept_id = d.id ",

            "and o.receiver_province = #{province} ",

            "and o.receiver_city in  ",
            " <foreach collection=\"cities\" item=\"cities\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{cities}",
            "</foreach>",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and d.name= #{teamName} ",
            "</if> ",

            "and o.order_time BETWEEN #{startDate} and #{endDate}",
            "</where> ",
            "GROUP BY ",
            "  o.receiver_province ,o.receiver_city  ",
            "</script>",
    })
    List<DeptDto> findOrderMoth(@Param("province")String province,
                                @Param("cities")List<String> cityList,
                                @Param("status") String status,
                                @Param("startDate") LocalDateTime startDate,
                                @Param("endDate") LocalDateTime endDate,
                                @Param("teamName") String teamName);



}
